/**
 * 
 */
package com.expense.util;

import java.io.File;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;

import com.expense.dao.Dao;

 

/**
 * 数据库备份成Excel发邮件
 * @author xj
 *
 */
public class BackUpDB {
	public Logger logger = Logger.getLogger(this.getClass());
	
	
	/**
	 * 数据库备份 
	 * 数据生成Excel
	 * 发邮件
	 * ***/
	public void backUpTablet_Logo(ServletContext context,HttpServletRequest request) {
		boolean onceSend=ConfigUtil.dbbackup_onceSend;//是否一次性发送
		String [] dbNames= {};//表名
		if(ConfigUtil.readConfigDbNames){//如果配置读配置文件的表名
			dbNames=ConfigUtil.dbbackup_dbNames.split(";");
		}else{
			dbNames=this.getDbNames();//从数据库去查询出用户的表
		}
		String [] filePaths=new String[dbNames.length];//生成文件路径
		
		
		Dao dao=new Dao();
		for (int i = 0; i < dbNames.length; i++) {
			String sql="select * from "+dbNames[i];
			ResultSet rs=dao.selectData(sql);
			
			List list=new ArrayList();//单个表的数据集合 每行数据以一个数组存入次List
			if(rs!=null) {
		        try {
		        	ResultSetMetaData   rsmd=rs.getMetaData();  
					int columnCount=rsmd.getColumnCount();  //总列数
					String [] columnNames=new String [columnCount];//列名数组
					int [] columnTypes=new int [columnCount];//列类型数组
					for (int c = 1; c <= columnCount; c++) {
						columnNames[c-1]=rsmd.getColumnName(c);
						columnTypes[c-1]=rsmd.getColumnType(c);
					}
					list.add(columnNames);//数据添加第一行列名
		        	
		        	
		        	String[] strings=null;
					while(rs.next()){//遍历数据
						 strings=new String[columnNames.length];
						 for (int c = 0; c < columnNames.length; c++) {
							 String columnName=columnNames[c];
							 
							 
							 /*int columnType=columnTypes[c];
						 
							 if(Types.VARCHAR==columnType
									 ||Types.NVARCHAR==columnType){
								 strings[c]=rs.getString(columnName);
							 }else if(Types.INTEGER==columnType
									 ||Types.BIT==columnType){
								 strings[c]= rs.getInt(columnName)+"";
							 }else if (Types.SMALLINT == columnType) { 
								 strings[c]= rs.getShort(columnName)+"";// 此处如果调用rs.getObject方法会返回一个Integer型[DB2] 
							  } else if(Types.DATE == columnType) { 
								 strings[c]= rs.getDate(columnName)+"";
							  } else if(Types.DECIMAL==columnType
									  ||Types.FLOAT==columnType
									  ){
								strings[c]= rs.getFloat(columnName)+"";
							  }else if(Types.DOUBLE == columnType) { 
								 strings[c]= rs.getDouble(columnName)+"";
							  }*/
							 if(rs.getObject(c+1)!=null)
								 strings[c]=rs.getObject(c+1).toString();
							 else
								 strings[c]=null;
							  
							 
						 }
						 list.add(strings);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}	
			
				
				String filePath="";//设置生成Excel的文件路径
					if(context!=null){
						if(Path.isLinux){
							filePath=context.getRealPath("/upload")+ "/"+dbNames[i]+".xls";
						}else{
							filePath=context.getRealPath("\\upload")+ "\\"+dbNames[i]+".xls";
						}
					}else if(request!=null){
						if(Path.isLinux){
							filePath=request.getRealPath("/upload")+ "/"+dbNames[i]+".xls";
						}else{
							filePath=request.getRealPath("\\upload")+ "\\"+dbNames[i]+".xls";
						}
					}
				filePaths[i]=filePath;
				
				String [] sheetNames= {dbNames[i]+"表数据"};//excle sheet名称
				List [] lists= {list};
				CreatExcel.creatExcelFile(sheetNames, lists,filePath);//创建Excel文件
			}
			dao.CloseCon();//关闭此次连接
		
		 
			
			MailBean mailBean=new MailBean();
			
			SimpleDateFormat sdFormat=new SimpleDateFormat("yyyy-MM-dd");
			Date date=new Date();
			mailBean.setTitle("("+sdFormat.format(date)+")表备份");
			mailBean.setContent("("+sdFormat.format(date)+")表备份数据,请查收！");
			
			String [] toUsers= ConfigUtil.dbBackUpToEmail.split(";");//得到配置的要发给的Email用户
			mailBean.setTosUser(toUsers);
			
			File file=null;
			if(onceSend) {//一次发送
				List files=new ArrayList();
				for (int j = 0; j < filePaths.length; j++) {
					try {
						file=new File(filePaths[j]);
						files.add(file);
					} catch (Exception e) {
						// TODO: handle exception
					}
				}
				
				mailBean.setFiles(files);
				boolean sendIfalg= MailUtil.sendMail(mailBean);
				 if (sendIfalg) {
					 logger.info("备份数据库邮件发送成功！");
				 }else {
					 logger.info("备份数据库邮件发送失败！");
				 }
				
			}else{//多次发送
				for (int j = 0; j < filePaths.length; j++) {
					List files=new ArrayList();
					try {
						file=new File(filePaths[j]);
						files.add(file);
						
						mailBean.setFiles(files);
						boolean sendIfalg= MailUtil.sendMail(mailBean);
						 
						 if (sendIfalg) {
							 logger.info("备份表"+dbNames[j]+"邮件发送成功！");
						 }else {
							 logger.info("备份表"+dbNames[j]+"邮件发送失败！");
						 }
						
					} catch (Exception e) {
						// TODO: handle exception
					}
					
					
					
				}	
				
			}
			
			
			//删除生成的临时文件
			for (int j = 0; j < filePaths.length; j++) {
				try {
					file=new File(filePaths[j]);
					if(file!=null&&file.exists()){
						file.delete();
					}
				} catch (Exception e) {
					// TODO: handle exception
				}
			}

			
			
			
	} 

	
	/***从数据库查询出要备份的用户表名**/
	public String[] getDbNames(){
		Dao dao=new Dao();
		String dbSql="";
		if (SqlConfig.MSQL_DB_Type.equals(SqlConfig.SQL_SERVER)) {
			dbSql="select name from sysobjects where xtype = 'U' order by name ";
			//dbSql="select table_name  from information_schema.tables where table_TYPE='BASE TABLE'";
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.ORACLE)) {
			dbSql="select table_name from user_tables order by table_name";
			//dbSql="select table_name from dba_tables where owner='"+SqlConfig.MSQL_DB_USER+"'";
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.MYSQL)) {
			dbSql="show tables";
			//System.out.println(rs.getString(“ename"));
		}else if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.ACCESS)) {
			 dbSql="select name from MSysObjects where  type=1 and flags=0 order by name";
		}
		ResultSet dbrs=dao.selectData(dbSql);
		
		List<String> dbNamesList=new ArrayList<String>();
		if(dbrs!=null) {
			try { 
				while(dbrs.next()){
					 if(SqlConfig.MSQL_DB_Type.equals(SqlConfig.MYSQL)) {
						 dbNamesList.add(dbrs.getString("ename"));
					 }else{
						 dbNamesList.add(dbrs.getString(1));
					 }
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		dao.CloseCon();//关闭连接
		
		String [] dbNames=new String[dbNamesList.size()];
		for (int i = 0; i < dbNamesList.size(); i++) {
			dbNames[i]=dbNamesList.get(i);
		}
		return dbNames;
	}

}
	
